<?php
/*
PHP REST SQL: A HTTP REST interface to relational databases
written in PHP

mysql.php :: MySQL database adapter
Copyright (C) 2004 Paul James <paul@peej.co.uk>

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

/* $id$ */

/**
 * PHP REST MySQL class
 * MySQL connection class.
 */
class mysql {
    
    /**
     * @var resource Database resource
     */
    var $db;
    
    /**
     * Connect to the database.
     * @param str[] config
     */
    function connect($config) {
        if ($this->db = @mysql_pconnect(
			$config['server'],
			$config['username'],
			$config['password']
		)) {
		    mysql_set_charset("utf8");
			if ($this->select_db($config['database'])) {
				return TRUE;
			}
        }
        return FALSE;
    }

    /**
     * Close the database connection.
     */
    function close() {
        mysql_close($this->db);
    }
    
    /**
     * Use a database
     */
    function select_db($database) {
        if (mysql_select_db($database, $this->db)) {
            return TRUE;
        }
        return FALSE;
    }
    
    /**
     * Get the columns in a table.
     * @param str table
     * @return resource A resultset resource
     */
    function getColumns($table) {
        return mysql_query(sprintf('SHOW COLUMNS FROM %s', $table), $this->db);
    }
    
    /**
     * Get the rows in a table.
     * @param str fields The names of the columns to return
     * @param str table
     * @return resource A resultset resource
     */
    function get($table, $filter, $sort, $limit) {
        
        // Check, if filter is a single value or an array.
        // If it is a single value, it is the uid of the row and we have to 
        // create an array.
        $pairs = NULL;
        
        if (isset($filter)) {
            if (is_array($filter) == false) {
                $pairs[$this->getPrimaryKey($table)] = $filter;
            } else {
                $pairs = $filter;
            }
        }
        
        $query = sprintf('SELECT SQL_CALC_FOUND_ROWS * FROM %s', $table);
        
        if (isset($pairs)) {
            $query .= ' WHERE '.$this->query($pairs);
        }
        
        if (isset($sort) && $sort != '') {
          $query .= sprintf(' ORDER BY %s', $sort);
        }
        
        if (isset($limit) && $limit != '') {
          $query .= sprintf(' LIMIT %s', $limit);
        }
        
        $result = mysql_query($query);
        $response = $this->createArrayFromResult($result);
        
        return $response;
    }

    /**
     * Get the result size of a batched query.
     * Must be called directly after getTable!
     * @return number of total rows.
     */
    function getResultSize() {
        $sql = "SELECT FOUND_ROWS() AS `found_rows`;";
        $rows = mysql_query($sql);
        $rows = mysql_fetch_assoc($rows);
        return $rows['found_rows'];
    }    

    /**
     * Get the tables in a database.
     * @return resource A resultset resource
     */
    function getDatabase() {
        return mysql_query('SHOW TABLES');
    }

    /**
     * Get the primary key for the request table.
     * This lib only works with tables that have a primary key with only one 
     * field.
     * @return str The primary key field names
     */
    function getPrimaryKey($table) {
        $resource = $this->getColumns($table);
        $primary = NULL;
        if ($resource) {
            while ($row = $this->row($resource)) {
                if ($row["Key"] == "PRI") {
                    $primary[] = $row["Field"];
                }
            }
        }
        
        if (count($primary) > 1) {
            return NULL;
        }
        
        return $primary[0];
    }
    
    /**
     * Create a query string from filter values.
     */
    function query($filter) {
        $query = '';
        if (isset($filter) && $filter != '') {
            $query = " (";
            foreach ($filter as $key => $value) {
                $query .= sprintf('(%s = "%s") AND ', $key, $value);
            }    
            $query = substr($query, 0, -5);
            $query .= ")";
        }    
        return $query;
    }
    
    /**
     * Update a row.
     * @param str table
     * @param str values
     * @param str where
     * @return bool
     */
    function updateRow($table, $uid, $pairs) {
        
        $values = '';
        foreach ($pairs as $column => $data) {
            $values .= '`'.$column.'` = "'.$this->escape($data).'", ';
        }
        $values = substr($values, 0, -2);    
        
        $key[$this->getPrimaryKey($table)] = $uid;
        
        $query = sprintf('UPDATE %s SET %s WHERE %s', $table, $values, $this->query($key));
        
        $resource = mysql_query($query);

        if (mysql_affected_rows($this->db) > 0) {
            $result = $this->get($table, $uid, NULL, NULL);
        }
        $result["rows"] = $result["rows"][0];
        return $result;
        
    }
    
    /**
     * Insert a new row.
     * @param str table
     * @param str names
     * @param str values
     * @return bool
     */
    function insertRow($table, $pairs) {
        
        foreach ($pairs as $key => $value) {
            $pairs[$key] = mysql_real_escape_string($value);
        }
        
        $values = join('", "', $pairs);
        $names = join('`, `', array_keys($pairs));

        $response = mysql_query(sprintf('INSERT INTO %s (`%s`) VALUES ("%s")', $table, $names, $values));

        $uid = $this->lastInsertId(); 

        $result = $this->get($table, $uid, NULL, NULL);
        $result["rows"] = $result["rows"][0]; 
        return $result;
    }
    
    /**
     * Get the columns in a table.
     * @param str table
     * @return resource A resultset resource
     */
    function deleteRow($table, $uid) {
        $pairs[$this->getPrimaryKey($table)] = $uid;
        return mysql_query(sprintf('DELETE FROM %s WHERE %s', $table, $this->query($pairs)));
    }
    
    /**
     * Escape a string to be part of the database query.
     * @param str string The string to escape
     * @return str The escaped string
     */
    function escape($string) {
        return mysql_real_escape_string($string);
    }
    
    /**
     * Fetch a row from a query resultset.
     * @param resource resource A resultset resource
     * @return str[] An array of the fields and values from the next row in the resultset
     */
    function row($resource) {
        return mysql_fetch_assoc($resource);
    }

    /**
     * The number of rows in a resultset.
     * @param resource resource A resultset resource
     * @return int The number of rows
     */
    function numRows($resource) {
        return mysql_num_rows($resource);
    }

    /**
     * The number of rows affected by a query.
     * @return int The number of rows
     */
    function numAffected() {
        return mysql_affected_rows($this->db);
    }
    
    /**
     * Get the ID of the last inserted record. 
     * @return int The last insert ID
     */
    function lastInsertId() {
        return mysql_insert_id();
    }
    
    function createArrayFromResult($result) {
        $row_result = array();        
        while ($row = $this->row($result)) {
            $values = array();
            foreach ($row as $column => $data) {
                $values[$column] = $data;
            }
            $row_result[] = $values; 
        }    
        
        $response["rows"]  = $row_result;
        $response["total"] = $this->getResultSize();
        
        return $response;
    }
    
}
?>
